<?php
/**
 * Created by PhpStorm.
 * User: dreamy
 * Date: 2019-05-25
 * Time: 11:16
 */
namespace common\services;

use common\models\TallyCategory;
use common\models\TallyRecord;
use yii\db\Query;
use yii\helpers\ArrayHelper;

/**
 * Class TallyStatService
 *
 * @package common\services
 * @desc 账本统计相关
 */
class TallyStatService extends BaseService
{

    /**
     * 获取日常统计图数据
     * 2021.01.15 增加转账情况的账户 收支统计 复杂条件
     * @param $tallyId int 账本ID
     * @param int $uid  用户ID
     * @param array $condition array 其他条件数组
     * @return array
     *
     */
    public static function getDailyData($tallyId,$uid=null,$condition=[])
    {
        $condition['tally_id'] =  $tallyId;
        if($uid){
            $condition['uid'] = $uid ; //uid可选
        }
        // 这里判断日期 如果是带开始|结束日期的 要结束+1天 保证右侧边界能包含进来
        if(isset($condition['end_date'])){
            $condition['end_date'] = date('Y-m-d',strtotime('+1 day',strtotime($condition['end_date'])));
        }
        $accountIds = ArrayHelper::getValue($condition,'account_ids','');
        if($accountIds){
            if(!is_array($accountIds)) $accountIds = explode(',',$accountIds);
        }
        // 先取出收入统计数据
        $query = self::getQuery($condition,false);
        $queryOut = clone $query;
        if($accountIds){
            // 如果传有账户 继续复杂组合查询
            $statListIn = $query->andFilterWhere(['or',
                ['and',['=','type',TallyRecord::TYPE_IN],['in','account_id',$accountIds]],
                ['and',['=','type',TallyRecord::TYPE_TRANS],['in','account_dst_id',$accountIds]],
            ])->asArray()->all();
        }else{
            $statListIn = $query
                ->andFilterWhere(['=','type',TallyRecord::TYPE_IN])
                ->asArray()->all();
        }
//        echo $query->createCommand()->getRawSql();die;
        // 循环遍历 制作成图表要求格式
        $inSummary = self::formatStatList($statListIn);
        // 取出支出图表数据
        if($accountIds){
            // 如果传有账户 继续复杂组合查询
            $statListOut = $queryOut
                ->andFilterWhere(['in','type',[TallyRecord::TYPE_OUT,TallyRecord::TYPE_TRANS]])
                ->andFilterWhere(['in','account_id',$accountIds])
                ->asArray()->all();
        }else{
            $statListOut = $queryOut
                ->andFilterWhere(['=','type',TallyRecord::TYPE_OUT])
                ->asArray()->all();
        }
        $outSummary = self::formatStatList($statListOut);
        return [
            'inAmount' => $inSummary['total'],
            'inlst' => $inSummary['data'],
            'maxI' => $inSummary['max'],
            'outAmount' => $outSummary['total'],
            'outlist' => $outSummary['data'],
            'maxO' => $outSummary['max'],
            'debtAmount' => ToolService::groupMathAdd([
                $inSummary['total'],
                -1 * $outSummary['total']
            ]),
        ];
    }

    /**
     * @param $data
     *
     * @return array
     * @desc 格式化统计数据
     */
    protected static function formatStatList($data)
    {
        $list = [];
        $total = 0;
        $max = 0;
        foreach ($data as $dk => $dv) {
            $dv['total'] = ToolService::moneyFormat($dv['total']);
            // 一级栏目是否存在
            if(!isset($list[$dv['category_pid']])) {
                $list[$dv['category_pid']] = [
                    'id' => $dv['category_pid'],
                    'name' => TallyCategoryService::getTitleGlobal($dv['category_pid']),
                    'total' => 0,
                    'c'=>[]
                ];
            }
            // 二级栏目是否存在
            if(!isset($list[$dv['category_pid']]['c'][$dv['category_id']])){
                $list[$dv['category_pid']]['c'][$dv['category_id']] = [];
            }
            // 添加二级栏目
            $list[$dv['category_pid']]['c'][$dv['category_id']] = [
                'id' => $dv['category_id'],
                'name' => TallyCategoryService::getTitleGlobal($dv['category_id']),
                'amount' => ToolService::moneyFormat($dv['total']),
            ];
            // 金额汇总
            $list[$dv['category_pid']]['total'] = ToolService::moneyFormat(
                bcadd($list[$dv['category_pid']]['total'],$dv['total']
                ));
            $total = $total + $dv['total'];
            $max = $dv['total'] > $max ? $dv['total'] : $max;

        }
        // 重置Key值
        foreach ($list as &$lv) {
            $lv['c'] = array_values($lv['c']);
        }
        return [
            'data' => array_values($list),
            'total' => ToolService::moneyFormat($total),
            'max' => ToolService::moneyFormat($max)
        ] ;
    }

    /**
     * @param $tallyId int 账本ID
     * @param int $uid  用户ID
     * @param array $condition array 其他条件数组
     * @return array
     * 获取成员收支统计数据
     */
    public static function getMemberData($tallyId,$uid=null,$condition=[])
    {
        $condition['tally_id'] =  $tallyId;
        if($uid){
            $condition['uid'] = $uid ; //uid可选
        }
        // 先取出收入统计数据
        $condition['type'] = TallyRecord::TYPE_IN ;
        $query = self::getMemberQuery($condition);
        $statListIn = $query->asArray()->all();
        foreach ($statListIn as &$mv){
            $mv['name'] = TallyMemberService::getTitleById($mv['member_id']);
            $mv['name'] = $mv['name'] ?: '其他';
        }
        $inValues = ArrayHelper::getColumn($statListIn,'total');
        // 取出支出图表数据
        $condition['type'] = TallyRecord::TYPE_OUT ;
        $queryOut = self::getMemberQuery($condition);
        $statListOut = $queryOut->asArray()->all();
        foreach ($statListOut as &$mva){
            $mva['name'] = TallyMemberService::getTitleById($mva['member_id']);
            $mva['name'] = $mva['name'] ?: '其他';
        }
        $outValues = ArrayHelper::getColumn($statListOut,'total');
        return [
            'inAmount' => array_sum($inValues),
            'inlst' => $statListIn,
            'maxI' => empty($inValues) ? 0 : max($inValues),
            'outAmount' => array_sum($outValues),
            'outlist' => $statListOut,
            'maxO' => empty($outValues) ? 0 : max($outValues),
        ];
    }

    /**
     * @param $tallyId int 账本ID
     * @param int $uid  用户ID
     * @param array $condition array 其他条件数组
     * @return array
     * 获取商家收支统计数据
     */
    public static function getStoreData($tallyId,$uid=null,$condition=[])
    {
        $condition['tally_id'] =  $tallyId;
        if($uid){
            $condition['uid'] = $uid ; //uid可选
        }
        // 先取出收入统计数据
        $condition['type'] = TallyRecord::TYPE_OUT ;
        $queryOut = self::getStoreQuery($condition);
        // 取出支出图表数据
        $statListOut = $queryOut->asArray()->all();
        foreach ($statListOut as &$mva){
            $mva['name'] = TallyStoreService::getTitleById($mva['store_id']);
            $mva['name'] = $mva['name'] ?: '其他';
        }
        $outValues = ArrayHelper::getColumn($statListOut,'total');
        return [
            'outAmount' => array_sum($outValues),
            'outlist' => $statListOut,
            'maxO' => empty($outValues) ? 0 : max($outValues),
        ];
    }

    /**
     * @param array $condition
     *
     * @return \yii\db\ActiveQuery
     * @desc 返回符合查询条件的query
     */
    protected static function getMemberQuery($condition)
    {
        $where = [];
        // 从DB获取
        $query = TallyRecord::find()
            ->select('sum(`amount`) as total,member_id')
            ->where($where);
        $query->groupBy('member_id');
        $query->orderBy('member_id ASC');
        // 用户ID
        $uid = ArrayHelper::getValue($condition,'uid','');
        if($uid){
            $query->andFilterWhere(['=','uid',$uid]);
        }
        // 账本ID
        $tallyId = ArrayHelper::getValue($condition,'tally_id','');
        if($tallyId){
            $query->andFilterWhere(['=','tally_id',$tallyId]);
        }
        // 类型收入、支出
        $type = ArrayHelper::getValue($condition,'type','');
        if($type){
            $query->andFilterWhere(['=','type',$type]);
        }
        // 时间段查询
        if(isset($condition['begin_date']) && isset($condition['end_date'])) {
            $query->andFilterWhere(['between','tally_time',$condition['begin_date'], $condition['end_date']]);
        }
        if(isset($condition['begin_date']) && !isset($condition['end_date'])) {
            $query->andFilterWhere(['>','tally_time',$condition['begin_date']]);
        }
        if(!isset($condition['begin_date']) && isset($condition['end_date'])) {
            $query->andFilterWhere(['<','tally_time',$condition['end_date']]);
        }
        return $query;

    }

    /**
     * @param array $condition
     *
     * @return \yii\db\ActiveQuery
     * @desc 返回符合查询条件的query
     */
    protected static function getStoreQuery($condition)
    {
        $where = [];
        // 从DB获取
        $query = TallyRecord::find()
            ->select('sum(`amount`) as total,store_id')
            ->where($where);
        $query->groupBy('store_id');
        $query->orderBy('store_id ASC');
        // 用户ID
        $uid = ArrayHelper::getValue($condition,'uid','');
        if($uid){
            $query->andFilterWhere(['=','uid',$uid]);
        }
        // 账本ID
        $tallyId = ArrayHelper::getValue($condition,'tally_id','');
        if($tallyId){
            $query->andFilterWhere(['=','tally_id',$tallyId]);
        }
        // 时间段查询
        if(isset($condition['begin_date']) && isset($condition['end_date'])) {
            $query->andFilterWhere(['between','tally_time',$condition['begin_date'], $condition['end_date']]);
        }
        if(isset($condition['begin_date']) && !isset($condition['end_date'])) {
            $query->andFilterWhere(['>','tally_time',$condition['begin_date']]);
        }
        if(!isset($condition['begin_date']) && isset($condition['end_date'])) {
            $query->andFilterWhere(['<','tally_time',$condition['end_date']]);
        }
        return $query;
    }

    /**
     * 计算月份差距
     * @param $date1
     * @param $date2
     *
     * @return float|int
     *
     */
    protected static function getMonthNum($date1,$date2)
    {
        $date1_stamp=strtotime($date1);
        $date2_stamp=strtotime($date2);
        list($date_1['y'],$date_1['m'])=explode("-",date('Y-m',$date1_stamp));
        list($date_2['y'],$date_2['m'])=explode("-",date('Y-m',$date2_stamp));
        return abs(($date_2['y']-$date_1['y'])*12 +$date_2['m']-$date_1['m']);
    }

    /**
     * 获取月度统计的 收入/支出/利润
     * 输出格式为
     * [date] => 2020-03
        [data] => Array
        (
        [in] => 0
        [out] => 0
        [pro] => 0
        )
     * @return array
     */
    public static function getStatMonthy($tallyId,$uid,$condition=[])
    {
        $result = [];
        $now = time();
        // 起始、终止月份
        $beginDateForm = ArrayHelper::getValue($condition,'begin_date');
        $endDateForm = ArrayHelper::getValue($condition,'end_date');
        $project_id = ArrayHelper::getValue($condition,'project_id','');
        if($beginDateForm && $endDateForm){
            $beginMonth = $beginDateForm;
            $endMonth = $endDateForm;
        }else{
            // 走默认最近一年的
            $beginMonth = date('Y-m',strtotime('-11 months',$now));
            $endMonth = date('Y-m',$now);
        }
        // 算出中间差距的月份
        $offsetMonth = self::getMonthNum($beginMonth,$endMonth);
        $offsetMonth ++ ;// 包含最后一个月
        for($i=0;$i<$offsetMonth;$i++){
            $month = date('Y-m',strtotime('+'.$i.' month', strtotime($beginMonth.'-01')));
            $temp['date'] = date('m',strtotime('+'.$i.' month', strtotime($beginMonth.'-01'))).'月';
            // 统计每月的收支情况
            $beginDate = $month.'-01'.' 00:00:00';
            $endDate   = date('Y-m-d H:i:s',strtotime('+1 month',strtotime($beginDate))-1 );
            // 获取$query 计算收入、支出、利润情况
            $query = TallyRecord::find()
                ->select('sum(`amount`) as total')
                ->where(['tally_id'=>$tallyId,'state'=>TallyRecord::STATE_VALID]);
            if($project_id != ''){
                $query = $query->andFilterWhere(['=','project_id',$project_id]);
            }
            $query->andFilterWhere(['between','tally_time',$beginDate,$endDate]);
            $queryOut = clone $query;
            $query->andFilterWhere(['=','type',TallyRecord::TYPE_IN]);
            $queryOut->andFilterWhere(['=','type',TallyRecord::TYPE_OUT]);
            $statIn  = $query->asArray()->one();
            $statOut = $queryOut->asArray()->one();
            $statIn  = intval(ArrayHelper::getValue($statIn,'total',0));
            $statOut = intval(ArrayHelper::getValue($statOut,'total',0));
            $statPro = $statIn - $statOut;
            $temp['data'] = [
                'in' => $statIn,
                'out' => $statOut,
                'pro'=>intval(bcsub($statIn,$statOut,2))
            ] ;
            $result[] = $temp;
        }
        return $result ;
    }

    /**
     * 获取开始与截止日期之间的每日数据统计
     * @param       $tallyId
     * @param       $uid
     * @param array $condition
     *
     * @return array
     * @throws \yii\db\Exception
     */
    public static function getStatDay($tallyId,$uid,$condition=[])
    {
        $now = time();
        // 没有月份按最近一月进行统计
        $beginDate = ArrayHelper::getValue($condition,'begin_date');
        $endDate = ArrayHelper::getValue($condition,'end_date');
        if(!$beginDate || !$beginDate){
            // 走默认最近一月的
            $beginDate = date('Y-m-d',strtotime('-1 month',$now));
            $endDate = date('Y-m-d');
        }
//        if((strtotime($endDate) - strtotime($beginDate)) > 3600*24*90){
//            return []; // 最多统计3个月
//        }
        // 子查询出每月数据
        $month_year = new \yii\db\Expression("DATE_FORMAT(`created_time`, '%Y-%m-%d') as dat");
        $subQuery = TallyRecord::find()
            ->select([
                $month_year,
                'amount',
                'type'
            ])
            ->where(['tally_id'=>$tallyId,'state'=>TallyRecord::STATE_VALID])
            ->andFilterWhere(['between','tally_time',$beginDate,$endDate]);
        // 这里区分收入和支出
        //$subQuery->andFilterWhere(['=','type',TallyRecord::TYPE_IN]);
        // 按天统计 按收支类型进行group 聚合
        $list = (new Query())->select('dat,type,sum(`amount`) as total')
            ->from(['dayc' => $subQuery])
            ->groupBy('dat,type')
            ->createCommand()
            ->queryAll();
        // 遍历获取收支情况
        $inArr = [] ;
        $outArr = [] ;
        foreach ($list as $lv){
            $mk = $lv['dat'];
            if($lv['type'] == TallyRecord::TYPE_IN){
                // 记录到收入
                $inArr[$mk] = $lv['total'];
            }else if($lv['type'] == TallyRecord::TYPE_OUT){
                // 记录到支出
                $outArr[$mk] = $lv['total'];
            }
        }
        // 遍历每天获取最终整合数据
        $result = [];
        $startT = strtotime($beginDate);
        $endT = strtotime($endDate);
        while (true){
            if($startT > $endT) break;
            $tk = date('Y-m-d',$startT);
            $in = ArrayHelper::getValue($inArr,$tk,0);
            $out = ArrayHelper::getValue($outArr,$tk,0);
            $pro = bcadd($in,-1*$out,2);
            $temp = [
                'date' => $tk,
                'data' => [
                    'in' => $in,
                    'out' => $out,
                    'pro' => $pro,
                ],
            ];
            $result[] = $temp;
            $startT = $startT + 3600*24;
        }
        return $result;
    }

    /**
     * 返回符合查询条件的query
     * 增加逻辑 账户的筛选 转入 与 转出账户 都要包含
     * @param array $condition
     * @param bool $accountContain 是否包含账户相关的条件处理 | 统计的时候就需要自行组装账户条件
     *
     * @return \yii\db\ActiveQuery
     * @desc
     */
    protected static function getQuery($condition,$accountContain=true)
    {
        $where = [];
        // 从DB获取
        $query = TallyRecord::find()
            ->select('sum(`amount`) as total,category_id,category_pid')
            ->where($where);
//            ->andWhere(['<>','category_pid','90']);
        $query->groupBy('category_id,category_pid');
        $query->orderBy('category_id ASC');
        // 用户ID
        $uid = ArrayHelper::getValue($condition,'uid','');
        if($uid){
            $query->andFilterWhere(['=','uid',$uid]);
        }
        // 账本ID
        $tallyId = ArrayHelper::getValue($condition,'tally_id','');
        if($tallyId){
            $query->andFilterWhere(['=','tally_id',$tallyId]);
        }
        // 账目类别
        $type = ArrayHelper::getValue($condition,'type','');
        if($type){
            $query->andFilterWhere(['=','type',$type]);
        }
        // 上个id
        $last_id = ArrayHelper::getValue($condition,'last_id','');
        if($last_id){
            $query->andFilterWhere(['>','id',$last_id]);
        }
        // 分类ID集合
        $categoryIds = ArrayHelper::getValue($condition,'category_ids','');
        if($categoryIds){
            $query->andFilterWhere(['in','category_id',is_array($categoryIds) ? $categoryIds : explode(',',$categoryIds)]);
        }
        // 单个分类
        $categoryId = ArrayHelper::getValue($condition,'category_id','');
        if($categoryIds){
            $query->andFilterWhere(['in','category_id',$categoryId]);
        }
        if($accountContain){
            // 账户ID集合
            $accountIds = ArrayHelper::getValue($condition,'account_ids','');
            if($accountIds){
                if(!is_array($accountIds)) $accountIds = explode(',',$accountIds);
                $query->andFilterWhere(['in','account_id',$accountIds]);
            }
        }
        // 商家ID集合
        $storeIds = ArrayHelper::getValue($condition,'store_ids','');
        if($storeIds){
            if(!is_array($storeIds)) $storeIds = explode(',',$storeIds);
            $query->andFilterWhere(['in','store_id',$storeIds]);
        }
        // 项目ID集合
        $projectIds = ArrayHelper::getValue($condition,'project_ids','');
        if($projectIds){
            $query->andFilterWhere(['in','project_id',is_array($projectIds) ? $projectIds : explode(',',$projectIds)]);
        }
        // 成员ID集合
        $memberIds = ArrayHelper::getValue($condition,'member_ids','');
        if($memberIds){
            $query->andFilterWhere(['in','member_id',is_array($memberIds) ? $memberIds : explode(',',$memberIds)]);
        }
        // 时间段查询
        if(isset($condition['begin_date']) && isset($condition['end_date'])) {
            $query->andFilterWhere(['between','tally_time',$condition['begin_date'], $condition['end_date']]);
        }
        if(isset($condition['begin_date']) && !isset($condition['end_date'])) {
            $query->andFilterWhere(['>','tally_time',$condition['begin_date']]);
        }
        if(!isset($condition['begin_date']) && isset($condition['end_date'])) {
            $query->andFilterWhere(['<','tally_time',$condition['end_date']]);
        }
        // 备注关键词
        $memo = ArrayHelper::getValue($condition,'memo','');
        if($memo){
            $query->andFilterWhere(['like','memo',$memo]);
        }
        // 包含状态
        $state = ArrayHelper::getValue($condition,'state');
        if($state !== null){
            $query->andFilterWhere(['=','state',$state]);
        }
        return $query;
    }
}
